<?php
/**
 * Created by PhpStorm.
 * User: LFL
 * Date: 2010/5/25
 * Time: 16:02
 */
namespace backend\controllers;

use Yii;
use yii\web\AdminController;
use backend\models\Warehouse;
use backend\models\Shop;
use backend\models\Bill;
use common\helps\tools;
use common\helps\api_jd;
use PHPExcel;


class BillController extends AdminController
{
    public $layout = false;
    //业务关键字
    public $service = 'bill';

    public function actionIndex()
    {
        //仓库
        $warehouse_where = ['useFlg' => 1];
        $warehouse = Warehouse::find()->where($warehouse_where)->select('id,warehouseName')->asArray()->all();

        $sql = 'SELECT ShopID,ShopName FROM j_shop';
        $shop = Yii::$app->db->createCommand($sql)->queryAll();

        return $this->render('index', ['warehouse'=>$warehouse, 'shop' => $shop]);
    }

    //订单详情
    public function actionDetail()
    {
        $data = Yii::$app->request->get();
        //仓库
        $warehouse_where = ['useFlg' => 1];
        $warehouse = Warehouse::find()->where($warehouse_where)->select('id,warehouseName')->asArray()->all();

        //门店
        //$shop_where = ['IN', 'Status', [1, 2]];
        $shop = Shop::find()->select('ShopID,CityID,DistrictID,ShopName,Address')->asArray()->all();

        //订单信息
        $bill = null;
        if (isset($data['billNo']) && !empty($data['billNo'])) {
            $sql = "SELECT * FROM j_bill WHERE billNo='" . $data['billNo'] . "'";
            $bill = Yii::$app->db->createCommand($sql)->queryOne();

            $sql = "SELECT bd.*,ma.materielCode,ma.materielName,ma.warehouseId,wh.warehouseName,ma.storeFlg,
                    (select count(0) from j_bill_ext where billNo=bd.billNo and materielId=bd.materielId) as extFlg
                    FROM j_bill_dtl bd
                    INNER JOIN j_materiel ma ON bd.materielId=ma.id
                    INNER JOIN j_warehouse wh ON ma.warehouseId=wh.id WHERE bd.billNo='" . $data['billNo'] . "'";
            $billDtl = Yii::$app->db->createCommand($sql)->queryAll();
            $bill['dtl'] = $billDtl;

            $sql = "SELECT bd.*,ms.batch,ms.price FROM j_bill_batch bd
                    INNER JOIN j_materiel_stock ms ON bd.stockId=ms.id
                    WHERE bd.billNo='" . $data['billNo'] . "'";
            $billBatch = Yii::$app->db->createCommand($sql)->queryAll();
            $bill['batch'] = $billBatch;
        }
        return $this->render('detail', ['warehouse' => $warehouse, 'shop' => $shop, 'bill' => $bill, 'sendDate'=> date('Y-m-d')]);
    }

    //jax订单列表
    public function actionGetList()
    {
        $data = Yii::$app->request->post();
        $sql_list = 'SELECT b.*,wh.warehouseName,s.ShopName FROM j_bill b
                    INNER JOIN j_warehouse wh ON b.warehouseId=wh.id
                    INNER JOIN j_shop s ON b.shopId=s.ShopID WHERE 1=1 ';
        $sql_count = 'SELECT COUNT(0) CNT FROM j_bill b
                    INNER JOIN j_warehouse wh ON b.warehouseId=wh.id
                    INNER JOIN j_shop s ON b.shopId=s.ShopID WHERE 1=1 ';

        $sql = '';
        //订单状态
        if (isset($data['billStatus']) && in_array($data['billStatus'], array('0','1','2','3','9'))) {
            $sql .= ' AND b.billStatus=' . $data['billStatus'];
        }
        //搜索关键字
        if (isset($data['billNo']) && !empty($data['billNo'])) {
            $sql .= ' AND b.billNo like \''.$data['billNo'].'%\'';
        }
        //下单日期
        if (isset($data['billDate']) && !empty($data['billDate'])) {
            $dayArr = explode(' - ', $data['billDate']);
            $sql .= ' AND b.createTime >= "' . $dayArr[0] . '" AND b.createTime <="' . $dayArr[1] . ' 23:59:59"';
        }
        //仓库
        if (isset($data['warehouseId']) && !empty($data['warehouseId'])) {
            $sql .= ' AND b.warehouseId=' . $data['warehouseId'];
        }
        //物料名称
        if (isset($data['materielName']) && !empty($data['materielName'])) {
            $sql .= ' AND exists (select 1 from j_bill_dtl bd inner join j_materiel ma 
                    on bd.materielId=ma.id where bd.billNo=b.billNo and ma.materielName like \'%' . $data['materielName'] . '%\')';
        }
        //门店
        if (isset($data['shopId']) && !empty($data['shopId'])) {
            $sql .= ' AND b.shopId=' . $data['shopId'];
        }

        $list_count = Yii::$app->db->createCommand($sql_count . $sql)->queryOne();
        $count = $list_count['CNT'];

        $sort_string = $sort = '';
        if (isset($data['order']) && isset($data['order'][0]['column'])) {
            $sort_string .= empty($data['columns'][$data['order'][0]['column']]['data']) ? 'b.id' : 'b.' . $data['columns'][$data['order'][0]['column']]['data'];
            $sort = $data['order'][0]['dir'] == 'asc' ? 'asc' : 'desc';
        }
        $sql .= ' ORDER BY ' . $sort_string . ' ' . $sort;
        if ($data['start'] || $data['length']) {
            $sql .= ' limit ' . $data['start'] . ',' . $data['length'];
        }
        $list = Yii::$app->db->createCommand($sql_list . $sql)->queryAll();
        if (empty($list)) {
            $list = array();
        }

        //相关参数 http://datatables.club/manual/server-side.html
        $result['draw'] = intval($data['draw']);
        $result['recordsTotal'] = intval($count);
        $result['recordsFiltered'] = intval($count);
        $result['data'] = $list;
        //操作日志
        return \yii\helpers\Json::encode($result);
    }

    //匹配库存
    public function actionMatchStock()
    {
        $data = Yii::$app->request->post();

        if (!isset($data['warehouseId']) || !isset($data['materielData']) || !is_array($data['materielData'])) {
            $this->retJson('参数错误', 500);
        }
        $returnArr = null;
        $warehouseId = $data['warehouseId'];
        foreach ($data['materielData'] as $materiel) {

            $searchSql = "SELECT ms.id,ms.batch,ms.price,ms.qty FROM j_materiel_stock ms
                          INNER JOIN j_warehouse wh ON ms.warehouseId=wh.id
                          WHERE ms.qty > 0 AND wh.can_Frozen=1 AND ms.warehouseId='" . $warehouseId . "' AND ms.materielId='" . $materiel['materielId'] . "' ORDER BY ms.batch";
            $dtlStock = Yii::$app->db->createCommand($searchSql)->queryAll();

            $batch = [];
            $deliverQty = 0;
            $orderCnt = $materiel['qty'];
            if ($dtlStock) {
                foreach ($dtlStock as $stock) {
                    if ($stock['qty'] >= $orderCnt) {
                        $stock['useCnt'] = $orderCnt;
                        $orderCnt = 0;
                    } else {
                        $stock['useCnt'] = $stock['qty'];
                        $orderCnt -= $stock['qty'];
                    }
                    $batch[] = $stock;
                    $deliverQty += $stock['useCnt'];
                    if (!$orderCnt) break;
                }
            }

            if ($orderCnt) {
                $returnArr[$materiel['materielId']]['deliverQty'] = 0;
            } else {
                $returnArr[$materiel['materielId']]['deliverQty'] = $deliverQty;
                $returnArr[$materiel['materielId']]['batch'] = $batch;
            }
        }
        $this->retJson('', 200, $returnArr);
    }

    //保存订单数据
    public function actionSaveBill()
    {
        $data = Yii::$app->request->post();

        if (!isset($data['warehouseId']) || !isset($data['shopId']) || !isset($data['materiels'])
            || !is_array($data['materiels'])
        ) {
            $this->retJson('参数错误', 500);
        }

        //订单号
        if (isset($data['billNo']) && !empty($data['billNo'])) {
            $billNo = $data['billNo'];
        } else {
            $billNo = tools::getBillNo(date('Ymd'), 'seq_bill_no',6);
            $ck_bil_no= Yii::$app->db->createCommand("SELECT count(0) cnt FROM j_bill WHERE billNo='" . $billNo . "'")->queryOne();
            if($ck_bil_no['cnt']) {
                $this->retJson('网络异常，请重试', 500);
            }
        }


        $newTime = date('Y-m-d H:i:s');

        $model = new Bill();
        $totalQty = 0;
        $totalAmount = 0;
        $bill = $billDtl = $billBatch = array();
        foreach ($data['materiels'] as $materiels) {

            $deliverQty = 0;
            $deliverAmount = 0;
            $sql = "SELECT * FROM j_materiel WHERE id='" . $materiels['materielId'] . "'";
            $resMa = Yii::$app->db->createCommand($sql)->queryOne();
            if (!$resMa) {
                $this->retJson('物料信息有误', 500);
            }
            //无库存数据的物料
            if ($resMa['storeFlg']) {
                if (!isset($materiels['batch']) || !is_array($materiels['batch'])) {
                    $this->retJson('参数错误', 500);
                }

                foreach ($materiels['batch'] as $batch) {

                    //检查实时库存
                    $ch_stock_sql = 'SELECT qty FROM j_materiel_stock WHERE id=' . $batch['stockId'];
                    $stock = Yii::$app->db->createCommand($ch_stock_sql)->queryOne();

                    $ch_stock_sql = 'SELECT deliverQty FROM j_bill_batch WHERE billNo=\'' . $billNo . '\' AND stockId=' . $batch['stockId'];
                    $old_bill_stock = Yii::$app->db->createCommand($ch_stock_sql)->queryOne();
                    $oldDeliverQty = 0;
                    if ($old_bill_stock) $oldDeliverQty = $old_bill_stock['deliverQty'];
                    if (!$stock || ($stock['qty'] + $oldDeliverQty) < $batch['deliverQty']) {
                        $this->retJson('【' . $materiels['materielName'] . '】库存不足', 500);
                    }

                    //订单批次
                    $billBatch[$materiels['materielId']][] = array(
                        'creator' => $model->getCreator(),
                        'createTime' => $newTime,
                        'billNo' => $billNo,
                        'materielId' => $materiels['materielId'],
                        'stockId' => $batch['stockId'],
                        'batch' => $batch['batch'],
                        'deliverQty' => $batch['deliverQty'],
                        'receiptQty' => $batch['deliverQty'],
                        'price' => $batch['price'],
                        'deliverAmount' => round($batch['deliverQty'] * $batch['price'], 4),
                        'receiptAmount' => round($batch['deliverQty'] * $batch['price'], 4),
                    );
                    $deliverQty += $batch['deliverQty'];
                    $deliverAmount += round($batch['deliverQty'] * $batch['price'], 4);
                }
            } else {
                $deliverQty += $materiels['orderQty'];
                $deliverAmount += round($materiels['orderQty'] * $resMa['referPrice'], 4);
            }


            //订单明细
            $billDtl[] = array (
                'creator' => $model->getCreator(),
                'createTime' => $newTime,
                'billNo' => $billNo,
                'materielId' => $materiels['materielId'],
                'qty' => $materiels['orderQty'],
                'deliverQty' => $deliverQty,
                'deliverAmount' => $deliverAmount,
            );
            $totalQty += $deliverQty ? $deliverQty : $materiels['qty'];
            $totalAmount += $deliverAmount ? $deliverAmount : 0;
        }

        //订单主信息
        if (isset($data['billNo']) && !empty($data['billNo'])) {
            $bill['editor'] = $model->getCreator();
            $bill['editTime'] = $newTime;
        } else {
            $bill['billNo'] = $billNo;
            $bill['creator'] = $model->getCreator();
            $bill['createTime'] = $newTime;
            $bill['orderType'] = 2; //1前台 2后台
            $bill['userId'] = $model->getCreator();
        }

        $bill['reviewer'] = $model->getCreator();
        $bill['reviewTime'] = $newTime;
        $bill['billStatus'] = 1;
        $bill['warehouseId'] = $data['warehouseId'];
        $bill['shopId'] = $data['shopId'];
        $bill['address'] = $data['address'];
        $bill['orderDesc'] = isset($data['remark']) ? $data['remark'] : null;
        $bill['totalQty'] = $totalQty;
        $bill['totalAmount'] = $totalAmount;
        $bill['sendDate'] = isset($data['sendDate']) ? $data['sendDate'] : null;

        $transaction = Yii::$app->db->beginTransaction();
        try {

            if (isset($data['billNo']) && !empty($data['billNo'])) {
                $model::updateAll($bill, "billNo=:billNo", array(':billNo' => $billNo));
                //还原数据
                $resetSql = "UPDATE j_materiel_stock ms INNER JOIN j_bill_batch bb ON ms.id=bb.stockId
                             SET ms.freeze=ms.freeze-bb.deliverQty,ms.qty=ms.qty+bb.deliverQty
                             WHERE bb.billNo='" . $billNo . "' AND bb.deliverQty > 0;";
                $resetSql .= "DELETE FROM j_bill_dtl WHERE billNo='" . $billNo . "';";
                $resetSql .= "DELETE FROM j_bill_batch WHERE billNo='" . $billNo . "';";
                Yii::$app->db->createCommand($resetSql)->execute();

            } else {
                //添加订单主信息
                Yii::$app->db->createCommand()->insert('j_bill', $bill)->execute();
            }

            //添加订单明细
            foreach ($billDtl as $sd) {
                Yii::$app->db->createCommand()->insert('j_bill_dtl', $sd)->execute();
                $autoID[$sd['materielId']] = Yii::$app->db->getLastInsertId();
            }

            //添加订单批次数据
            $freSql = '';
            foreach ($billBatch as $mcode => $batchArr) {
                foreach ($batchArr as $sb) {
                    $freSql .= 'UPDATE j_materiel_stock SET freeze=freeze+' . $sb['deliverQty'] . ',qty=qty-' .
                        $sb['deliverQty'] . ' WHERE id=' . $sb['stockId'] . ';';

                    $sb['dtlId'] = $autoID[$mcode];
                    Yii::$app->db->createCommand()->insert('j_bill_batch', $sb)->execute();
                }
            }

            //冻结库存
            if($freSql) Yii::$app->db->createCommand($freSql)->execute();

            $transaction->commit();
            $this->retJson('操作成功', 200);
        } catch (\Exception $e) {
            $transaction->rollBack();
            error_log("异常:" . $e->getMessage() . $e->getLine());
            $this->retJson('操作失败', 500);
        }
    }

    //修改订单状态
    public function actionEditBillStatus()
    {
        $data = Yii::$app->request->post();
        if (!isset($data['billNo']) || !isset($data['status'])) {
            $this->retJson('参数错误', 500);
        }

        $model = new Bill();
        $sql = "SELECT * FROM j_bill WHERE billNo = '" . $data['billNo'] . "'";
        $res = Yii::$app->db->createCommand($sql)->queryOne();
        if ($data['status'] == 2 && $res['billStatus'] != 1) {
            $this->retJson('单据状态异常，请刷新后重试', 500);
        }
        if ($data['status'] == 9 && $res['billStatus'] != 1 && $res['billStatus'] != 0) {
            $this->retJson('单据状态异常，请刷新后重试', 500);
        }

        $bill['billStatus'] = $data['status'];
        if ($data['status'] == 2) {
            $bill['reviewer'] = $model->getCreator();
            $bill['reviewTime'] = date('Y-m-d H:i:s');
        }
        if ($data['status'] == 9) {
            $bill['editor'] = $model->getCreator();
            $bill['editTime'] = date('Y-m-d H:i:s');
        }
        $transaction = Yii::$app->db->beginTransaction();
        try {
            if ($data['status'] == 9 && $res['billStatus'] == 1) {
                $resetSql = "UPDATE j_materiel_stock ms INNER JOIN j_bill_batch bb ON ms.id=bb.stockId
                             SET ms.freeze=ms.freeze-bb.deliverQty,ms.qty=ms.qty+bb.deliverQty
                             WHERE bb.billNo='" . $data['billNo'] . "';";
                Yii::$app->db->createCommand($resetSql)->execute();
            }
            $model::updateAll($bill, "billNo=:billNo", array(':billNo' => $data['billNo']));
            $transaction->commit();
            $this->retJson('操作成功', 200);
        } catch (\Exception $e) {
            $transaction->rollBack();
            error_log("异常:" . $e->getMessage() . $e->getLine());
            $this->retJson('操作失败', 500);
        }
    }

    //批量发货
    public function actionBatchDeliver()
    {
        $data = Yii::$app->request->post();
        if (!isset($data['dataNo']) || !is_array($data['dataNo'])) {
            $this->retJson('参数错误', 500);
        }

        $model = new Bill();
        $newTime = date('Y-m-d H:i:s');
        foreach ($data['dataNo'] as $billNo) {
            $sql = "SELECT * FROM j_bill WHERE billNo = '" . $billNo . "' AND billStatus = 1";
            $bill = Yii::$app->db->createCommand($sql)->queryOne();
            if (!$bill) {
                $this->retJson('【' . $billNo . '】单据状态异常', 500);
            }

            $billArr[] = array(
                'billNo' => $billNo,
                'billStatus' => 2,
                'reviewer' => $model->getCreator(),
                'reviewTime' => $newTime
            );
        }

        if (isset($billArr)) {
            $transaction = Yii::$app->db->beginTransaction();
            try {
                foreach ($billArr as $b) {
                    $model::updateAll($b, ['billNo' => $b['billNo']]);
                }
                $transaction->commit();
                $this->retJson('操作成功', 200);
            } catch (\Exception $e) {
                $transaction->rollBack();
                error_log("异常:" . $e->getMessage() . $e->getLine());
                $this->retJson('操作失败', 500);
            }
        }
    }

    //审核订单
    public function actionAuditBill()
    {
        $data = Yii::$app->request->post();
        if (!isset($data['billNo']) || empty($data['billNo'])) {
            $this->retJson('参数错误', 500);
        }
        $billNo = $data['billNo'];

        $sql = "SELECT * FROM j_bill WHERE billNo = '" . $billNo . "' AND billStatus = 0";
        $bill = Yii::$app->db->createCommand($sql)->queryOne();
        if (!$bill) {
            $this->retJson('【' . $billNo . '】单据状态异常', 500);
        }

        $sql = "SELECT bd.id,bd.materielId,bd.qty,ma.storeFlg,ma.referPrice,ma.materielName FROM j_bill_dtl bd
                INNER JOIN j_materiel ma on bd.materielId=ma.id WHERE bd.billNo = '" . $billNo . "'";
        $bill_dtl = Yii::$app->db->createCommand($sql)->queryAll();

        $billData = array();
        $totalQty = $totalAmount = 0;
        $strMsg = '';
        foreach ($bill_dtl as $dtl) {
            $deliverQty = $deliverAmount = 0;
            if ($dtl['storeFlg']) {
                $orderCnt = $dtl['qty'];
                $searchSql = "SELECT ms.id,ms.batch,ms.price,ms.qty FROM j_materiel_stock ms
                              INNER JOIN j_warehouse wh ON ms.warehouseId=wh.id
                              WHERE ms.qty>0 AND wh.can_Frozen=1 AND ms.warehouseId='" . $bill['warehouseId'] . "' AND ms.materielId='" . $dtl['materielId'] . "' ORDER BY ms.batch";
                $dtlStock = Yii::$app->db->createCommand($searchSql)->queryAll();
                if (!$dtlStock) {
                    $strMsg .= '【' . $dtl['materielName'] . '】未找到库存<br>';
                    continue;
                }

                foreach ($dtlStock as $stock) {
                    if ($stock['qty'] >= $orderCnt) {
                        $stock['useCnt'] = $orderCnt;
                        $orderCnt = 0;
                    } else {
                        $stock['useCnt'] = $stock['qty'];
                        $orderCnt -= $stock['qty'];
                    }

                    $dtl['batch'][] = $stock;
                    $deliverQty += $stock['useCnt'];
                    $deliverAmount += round($stock['useCnt'] * $stock['price'], 4);
                    if (!$orderCnt) break;
                }

                if ($orderCnt) {
                    $strMsg .= '【' . $dtl['materielName'] . '】库存不足<br>';
                    continue;
                }

            } else {
                $deliverQty += $dtl['qty'];
                $deliverAmount += round($dtl['qty'] * $dtl['referPrice'], 4);
            }

            $dtl['deliverQty'] = $deliverQty;
            $dtl['deliverAmount'] = $deliverAmount;
            $billData[] = $dtl;

            $totalQty += $deliverQty;
            $totalAmount += $deliverAmount;
        }

        if($strMsg){
            $this->retJson($strMsg, 500);
        }

        $model = new Bill();
        $newTime = date('Y-m-d H:i:s');
        $transaction = Yii::$app->db->beginTransaction();
        try {

            foreach ($billData as $dtlData) {
                if (isset($dtlData['batch'])) {
                    foreach ($dtlData['batch'] as $batch) {
                        $saveBatch = array(
                            'creator' => $model->getCreator(),
                            'createTime' => $newTime,
                            'billNo' => $billNo,
                            'materielId' => $dtlData['materielId'],
                            'stockId' => $batch['id'],
                            'batch' => $batch['batch'],
                            'deliverQty' => $batch['useCnt'],
                            'receiptQty' => $batch['useCnt'],
                            'price' => $batch['price'],
                            'deliverAmount' => round($batch['useCnt'] * $batch['price'], 4),
                            'receiptAmount' => round($batch['useCnt'] * $batch['price'], 4),
                        );

                        $freeSql = 'UPDATE j_materiel_stock SET freeze=freeze+' . $batch['useCnt'] . ',qty=qty-' .
                            $batch['useCnt'] . ' WHERE id=' . $batch['id'];
                        Yii::$app->db->createCommand($freeSql)->execute();
                        Yii::$app->db->createCommand()->insert('j_bill_batch', $saveBatch)->execute();
                    }
                }

                $saveDtl = array(
                    'deliverQty' => $dtlData['deliverQty'],
                    'deliverAmount' => $dtlData['deliverAmount']
                );
                Yii::$app->db->createCommand()->update('j_bill_dtl', $saveDtl, ['id' => $dtlData['id']])->execute();
            }

            $saveBill = array(
                'billStatus' => 1,
                'totalQty' => $totalQty,
                'totalAmount' => $totalAmount
            );
            Yii::$app->db->createCommand()->update('j_bill', $saveBill, ['billNo' => $billNo])->execute();

            $transaction->commit();

            $this->SyncJD($data['billNo']);

            $this->retJson('操作成功', 200);
        } catch (\Exception $e) {
            $transaction->rollBack();
            elog("异常:" . $e->getMessage() . $e->getLine());
            $this->retJson('操作失败', 500);
        }
    }

    //查询收获备注、图片
    public function actionGetBillImg()
    {
        $data = Yii::$app->request->post();
        if (!isset($data['billNo']) || empty($data['billNo']) ||
            !isset($data['materielId']) || empty($data['materielId'])) {
            $this->retJson('参数错误', 500);
        }

        $result = array();

        $sql = "select filePath from j_bill_ext WHERE billNo='" . $data['billNo'] . "' AND materielId=" . $data['materielId'];
        $dtlImg = Yii::$app->db->createCommand($sql)->queryAll();
        $result['img'] = [];
        if ($dtlImg) {
            foreach ($dtlImg as &$img) {
                $result['img'][] = Yii::$app->params['WEB_IMG_URL'] . $img['filePath'];
            }
        }
        $sql = "select remark from j_bill_dtl WHERE billNo='" . $data['billNo'] . "' AND materielId=" . $data['materielId'];
        $dtlRemark = Yii::$app->db->createCommand($sql)->queryOne();
        $result['remark'] = $dtlRemark['remark'];
        $this->retJson('ok', 200, $result);
    }

    //导出数据
    public function actionExport()
    {
        ini_set("max_execution_time", 0);
        ini_set('memory_limit','1024M'); //临时设置最大内存占用为1G

        $data = Yii::$app->request->get();
        $sql = 'SELECT b.*,wh.warehouseName,s.ShopName FROM j_bill b
                    INNER JOIN j_warehouse wh ON b.warehouseId=wh.id
                    INNER JOIN j_shop s ON b.shopId=s.ShopID WHERE 1=1 ';

        //订单状态
        if (isset($data['billStatus']) && in_array($data['billStatus'], array('0','1','2','3','9'))) {
            $sql .= ' AND b.billStatus=' . $data['billStatus'];
        }
        //搜索关键字
        if (isset($data['billNo']) && !empty($data['billNo'])) {
            $sql .= ' AND b.billNo like \''.$data['billNo'].'%\'';
        }
        //下单日期
        if (isset($data['billDate']) && !empty($data['billDate'])) {
            $dayArr = explode(' - ', $data['billDate']);
            $sql .= ' AND b.createTime >= "' . $dayArr[0] . '" AND b.createTime <="' . $dayArr[1] . ' 23:59:59"';
        }
        //仓库
        if (isset($data['warehouseId']) && !empty($data['warehouseId'])) {
            $sql .= ' AND b.warehouseId=' . $data['warehouseId'];
        }
        //物料名称
        if (isset($data['materielName']) && !empty($data['materielName'])) {
            $sql .= ' AND exists (select 1 from j_bill_dtl bd inner join j_materiel ma 
                    on bd.materielId=ma.id where bd.billNo=b.billNo and ma.materielName like \'%' . $data['materielName'] . '%\')';
        }
        //门店
        if (isset($data['shopId']) && !empty($data['shopId'])) {
            $sql .= ' AND b.shopId=' . $data['shopId'];
        }

        $list = Yii::$app->db->createCommand($sql)->queryAll();
        if (empty($list)) {
            $list = array();
        }

        $objPHPExcel = new PHPExcel();
        $objSheet = $objPHPExcel->getActiveSheet();//获取到sheet
        $objSheet->setTitle("billexport");//设置sheet名字
        $objSheet->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置excel文件默认水平垂直方向居中
        $objSheet->getDefaultRowDimension()->setRowHeight(30);//设置默认行高
        $topStyle = array('borders' => array('top' => array('style' => \PHPExcel_Style_Border::BORDER_THIN)));
        $rigStyle = array('borders' => array('right' => array('style' => \PHPExcel_Style_Border::BORDER_THIN)));
        $objSheet
            ->setCellValue("A1", '订单编号')
            ->setCellValue("B1", "状态")
            ->setCellValue("C1", "仓库")
            ->setCellValue("D1", "门店")
            ->setCellValue("E1", "发货时间")
            ->setCellValue("F1", "下单人")
            ->setCellValue("G1", "下单时间")
            ->setCellValue("H1", "审核人")
            ->setCellValue("I1", "审核时间")
            ->setCellValue("J1", "数量")
            ->setCellValue("K1", "金额")
            ->setCellValue("L1", "物料ID")
            ->setCellValue("M1", "物料名称")
            ->setCellValue("N1", "所属仓库")
            ->setCellValue("O1", "订货数量")
            ->setCellValue("P1", "冻结数量")
            ->setCellValue("Q1", "发货数量")
            ->setCellValue("R1", "收货数量")
            ->setCellValue("S1", "收货差异数量");

        $i = 2;
        $statusName = [0=> '创建', 1=>'已审核',2=>'已发货',3=>'已收货',9=>'作废'];
        foreach ($list as $bill) {

            $sql = "SELECT bd.*,ma.materielName,ma.warehouseId,wh.warehouseName,ma.storeFlg
                    FROM j_bill_dtl bd
                    INNER JOIN j_materiel ma ON bd.materielId=ma.id
                    INNER JOIN j_warehouse wh ON ma.warehouseId=wh.id WHERE bd.billNo='" . $bill['billNo'] . "'";
            $billDtl = Yii::$app->db->createCommand($sql)->queryAll();
            foreach ($billDtl as $dtl) {

                $objSheet
                    ->setCellValueExplicit("A$i", $bill['billNo'], \PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValue("B$i", $statusName[$bill['billStatus']])
                    ->setCellValue("C$i", $bill['warehouseName'])
                    ->setCellValue("D$i", $bill['ShopName'])
                    ->setCellValue("E$i", $bill['sendDate'])
                    ->setCellValue("F$i", ''.$bill['userId'])
                    ->setCellValue("G$i", ''.$bill['createTime'])
                    ->setCellValue("H$i", ''.$bill['reviewer'])
                    ->setCellValue("I$i", ''.$bill['reviewTime'])
                    ->setCellValue("J$i", ''.$bill['totalQty'])
                    ->setCellValue("K$i", ''.$bill['totalAmount']);

                $freeQty = 0; $deliverQty = 0; $receiptQty = 0; $diffQty = 0;
                //审核后显示发货数量
                if($bill['billStatus'] && $bill['billStatus'] != 9) {
                    // 审核后&&管库存的物料显示冻结数量，审核后订单已更新发货数量 并等于冻结数量
                    if ($dtl['storeFlg']) {
                        $freeQty = $dtl['deliverQty'];
                    }
                    //发货
                    if ($bill['billStatus'] >= 2) {
                        $deliverQty = $dtl['deliverQty'];
                    }
                    //收货
                    if ($bill['billStatus'] == 3) {
                        $receiptQty = $dtl['receiptQty'];
                        $diffQty = $deliverQty - $receiptQty;
                    }
                }

                $objSheet
                    ->setCellValue("L$i", $dtl['materielId'])
                    ->setCellValue("M$i", $dtl['materielName'])
                    ->setCellValue("N$i", $dtl['warehouseName'])
                    ->setCellValue("O$i", $dtl['qty'])  //订货
                    ->setCellValue("P$i", $freeQty)     //冻结
                    ->setCellValue("Q$i", $deliverQty)  //发货
                    ->setCellValue("R$i", $receiptQty)  //收货
                    ->setCellValue("S$i", $diffQty);    //差异
                $i++;
            }
            $objSheet->getStyle("A$i:S$i")->applyFromArray($topStyle);
        }

        $objSheet->getColumnDimension('A')->setWidth(20);
        $objSheet->getColumnDimension('B')->setWidth(10);
        $objSheet->getColumnDimension('C')->setWidth(12);
        $objSheet->getColumnDimension('D')->setWidth(12);
        $objSheet->getColumnDimension('E')->setWidth(14);
        $objSheet->getColumnDimension('G')->setWidth(16);
        $objSheet->getColumnDimension('I')->setWidth(16);
        $objSheet->getColumnDimension('L')->setWidth(12);
        $objSheet->getColumnDimension('M')->setWidth(14);
        $objSheet->getColumnDimension('N')->setWidth(14);
        $objSheet->getStyle('A')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $objSheet->getStyle("K1:K$i")->applyFromArray($rigStyle);
        $objSheet->getStyle("A1:S1")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('6fc144');//填充背景颜色
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');//生成excel文件 如：Excel2007
        $fileName = 'data-' . time() . '.xls';
        $this->browser_export('Excel5', $fileName);//输出到浏览器 设置游览器保存文件名
        $objWriter->save("php://output");
    }

    function browser_export($type,$filename)
    {
        if ($type == "Excel5") {
            header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
        } else {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件
        }
        header('Content-Disposition: attachment;filename="' . $filename . '"');//告诉浏览器将输出文件的名称
        header('Cache-Control: max-age=0');//禁止缓存
    }

    //同步金蝶
    public function actionSyncJd()
    {
        $data = Yii::$app->request->post();
        if (!isset($data['billNo']) || empty($data['billNo'])) {
            $this->retJson('参数错误', 500);
        }
        $res = $this->SyncJD($data['billNo']);
        if ($res) {
            $this->retJson('同步成功', 200);
        }
        $this->retJson('同步失败', 500);
    }

    //同步金蝶
    private function SyncJD($billNo)
    {
        $postData = array();

        $sqlBill = "SELECT
            jb.shopId AS FCustIdtId,
            SUBSTR(jb.createTime,1,10) AS FDate,
            CASE WHEN js.needinvoice=0 THEN 'FALSE' WHEN js.needinvoice=1 THEN 'TRUE' ELSE 'False' END AS FIsIncludedTax,
            0 AS FShippingOrder,
            jb.warehouseId AS FWarHouseBel,
            jb.userId AS FOrdererName,
            jb.billNo AS FBillNo,
            jb.warehouseId AS FWarHouseId,
            jb.totalQty AS FQtyTotal,
            jb.totalAmount AS FReferAmount,
            jb.orderType AS FOrderType,
            jb.reviewer AS FReviewer,
            jb.reviewTime AS FReDate,
            jb.address AS FAdress,
            jb.receiver AS FConSignee,
            jb.mobile AS FConSigneeTel,
            jb.orderDesc AS FOrderNote,
            js.needstock AS FWarHouseIsIn,
            jb.sendDate
            FROM j_bill jb
            LEFT JOIN j_shop js ON jb.shopid=js.ShopID
            WHERE (jb.ext1<>1 OR jb.ext1 IS NULL) AND jb.billStatus=1
            AND jb.billNo ='" . $billNo . "' ";

        $bill = Yii::$app->db->createCommand($sqlBill)->queryOne();
        if(!$bill) return false;

        $sqlDtl = "SELECT 
            CONCAT(ma.id,'-',ma.materielCode) AS FMaterialId,
            bd.qty AS FQty,
            ma.taxRate AS FEntryTaxRate,
            ma.orderUnit AS FUnitID,
            ma.orderUnit AS FPriceUnitId,
            ma.salePrice, bdp.salePrice AS prePrice
            FROM j_bill_dtl bd
            LEFT JOIN j_materiel ma ON ma.id=bd.materielId
            LEFT JOIN j_bill_pre bp ON find_in_set(bd.billNo,bp.billNo)
            LEFT JOIN j_bill_dtl_pre bdp ON bp.preBillNo = bdp.preBillNo AND bd.materielId=bdp.materielId
            WHERE bd.billNo ='" . $billNo . "'";
        $billDtl = Yii::$app->db->createCommand($sqlDtl)->queryAll();

        $totalAmount = 0;
        foreach ($billDtl as $dtl) {
            if ($dtl['prePrice']) {
                $FPrice = $dtl['prePrice'];
                $totalAmount += $dtl['prePrice'] * $dtl['FQty'];
            } else {
                $FPrice = $dtl['salePrice'];
                $totalAmount += $dtl['salePrice'] * $dtl['FQty'];
            }
            $postData['FSaleOrderEntry'][] = array(
                'FMaterialId' => $dtl['FMaterialId'],
                'FPrice' => $FPrice,
                'FQty' => $dtl['FQty'],
                'FEntryTaxRate' => $dtl['FEntryTaxRate'],
                'FUnitID' => $dtl['FUnitID'],
                'FPriceUnitId' => $dtl['FPriceUnitId'],
                'FDeliveryDate' => $bill['sendDate']
            );
        }

        $postData['FCustId'] = $bill['FCustIdtId'];
        $postData['FSaleDeptId'] = 'FSaleDeptId';
        $postData['FSalerId'] = 'apiadmin_GW000001_1';
        $postData['FDate'] = $bill['FDate'];
        $postData['FIsIncludedTax'] = $bill['FIsIncludedTax'];
        $postData['FShippingOrder'] = $bill['FShippingOrder'];
        $postData['FWarHouseIsIn'] = $bill['FWarHouseIsIn'];
        $postData['FRemAmount'] = $totalAmount;
        $postData['FPaidAmount'] = $totalAmount;
        $postData['FWarHouseBel'] = $bill['FWarHouseBel'];
        $postData['FOrdererName'] = $bill['FOrdererName'];
        $postData['FBillNo'] = $bill['FBillNo'];
        $postData['FWarHouseId'] = $bill['FWarHouseBel'];
        $postData['FQtyTotal'] = $bill['FQtyTotal'];
        $postData['FReferAmount'] = $bill['FReferAmount'];
        $postData['FOrderType'] = $bill['FOrderType'];
        $postData['FReviewer'] = $bill['FReviewer'];
        $postData['FReDate'] = $bill['FReDate'];
        $postData['FAdress'] = $bill['FAdress'];
        $postData['FConSignee'] = $bill['FConSignee'];
        $postData['FConSigneeTel'] = $bill['FConSigneeTel'];
        $postData['FOrderNote'] = $bill['FOrderNote'];

        $data = api_jd::post('AddSaleOrder', $postData);
        if (isset($data['errCode']) && $data['errCode'] == 1) {
            $saveBill = array('ext1' => 1);
            $res = true;
        } else {
            $saveBill = array('ext1' => 0);
            $res = false;
        }
        Yii::$app->db->createCommand()->update('j_bill', $saveBill, ['billNo' => $billNo])->execute();

        return $res;
    }

    public function actionTongbu()
    {
        return $this->render('tongbu');
    }
    public function actionSync2()
    {
        ini_set('max_execution_time', 300); //300 seconds = 5 minutes
        $data = Yii::$app->request->post();
        if (!isset($data['billNo']) || empty($data['billNo'])) {
            $this->retJson('参数错误', 500);
        }
        $msg = "";
        $var = str_replace(PHP_EOL, '', $data['billNo']);
        $var = explode(",",$var);

        foreach ($var as $billNo) {
            $billNo = trim($billNo);
            if(empty($billNo)) continue;
            $res = $this->SyncJD($billNo);
            if (!$res) {
                $msg .= $billNo."<br/>";
            }
        }

        if($msg){
            $this->retJson("失败订单<br>".$msg, 500);
        }else{
            $this->retJson('同步成功', 200);
        }
    }

}